package com.itcast.excel.test;

import com.alibaba.dubbo.common.json.JSON;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelRead {
    public static void main(String[] args) {
        read();
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String format = simpleDateFormat.format(1574133926000l);
        System.out.println(format);

    }

    public static void read() {
        OutputStream outputStream=null;
        ExcelWriter excelWriter=null;
        InputStream inputStream=null;
        try {
          /*  Map<String, List<List<String>>> map1 = new HashMap<>();
            new Thread(new SubRunnable(map1)).start();
            new Thread(new SubRunnable(map1)).start();
            new Thread(new SubRunnable(map1)).start();*/
           inputStream = new BufferedInputStream(new FileInputStream(new File("C:\\Users\\李赛飞\\Desktop\\11年.xlsx")));
            Sheet sheet = new Sheet(1, 1);
            List<Object> list = EasyExcelFactory.read(inputStream, sheet);

            Set<String> set = new HashSet<>();
            Map<String, List<List<String>>> map1 = new HashMap<>();

            for (Object o : list) {
                List<List<String>> listList = new ArrayList<>();
                List<String> list1 = (List<String>) o;
                boolean b = set.add(list1.get(7));
                if (!b) {
                    map1.get(list1.get(7)).add(list1);
                } else {
                    listList.add(list1);
                    map1.put(list1.get(7), listList);
                }
            }
            Map<String, List<String>> map = new HashMap<>();

            for (String s : map1.keySet()) {
                List<String> list1 = new ArrayList<>();
                double n_personSum = 0;
                double n_vehicleSum = 0;
                double n_license = 0;
                //n_licence p	n_emp_ft z	n_emp_pt z	n_emp_home z	n_student z	n_hhld_trip p
                double n_emp_ft_zSum = 0;
                double n_emp_homeSum = 0;
                double n_studentSum = 0;
                double n_hhld_tripSum = 0;
                double n_emp_ptSum = 0;
                int i = 0;
                for (List<String> stringList : map1.get(s)) {
                    n_personSum += Double.parseDouble(stringList.get(4));
                    n_vehicleSum += Double.parseDouble(stringList.get(5));
                    n_license += Double.parseDouble(stringList.get(14));
                    n_emp_ft_zSum += Double.parseDouble(stringList.get(15));
                    n_emp_ptSum += Double.parseDouble(stringList.get(16));
                    n_emp_homeSum += Double.parseDouble(stringList.get(17));
                    n_studentSum += Double.parseDouble(stringList.get(18));
                    n_hhld_tripSum += Double.parseDouble(stringList.get(19));
                    i++;
                }
               /* list1.add(s + "n_person和为" + n_personSum);
                list1.add(s + "n_vehicleS平均数为" + n_vehicleSum / i);
                list1.add(s + "n_license" + n_license / i);
                list1.add(s + "n_emp_ft_zSum和为" + n_emp_ft_zSum);
                list1.add(s + "n_emp_home和为" + n_emp_homeSum);
                list1.add(s + "n_student和为" + n_studentSum);
                list1.add(s + "n_hhld_trip和为" + n_hhld_tripSum);
                list1.add(s + "n_emp_pt平均数为" + n_emp_ptSum / i);*/
                list1.add(""+ n_personSum);
                list1.add("" + n_vehicleSum / i);
                list1.add(""+ n_license / i);
                list1.add("" + n_emp_ft_zSum);
                list1.add("" + n_emp_homeSum);
                list1.add("" + n_studentSum);
                list1.add(""+ n_hhld_tripSum/i);
                list1.add("" + n_emp_ptSum );
                list1.add(s);
                map.put(s, list1);
                //excel的生成
                 outputStream=new BufferedOutputStream(new FileOutputStream(new File("C:\\Users\\李赛飞\\Desktop\\xxxx.xlsx")));
                 excelWriter=new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
                List<ExcelModel> excelModels=new ArrayList<>();
                for (String s1 : map.keySet()) {
                   ExcelModel excelModel=new ExcelModel();
                    List<String> list2 = map.get(s1);
                    excelModel.setN_personSum(list2.get(0));
                    excelModel.setN_vehicleSum(list2.get(1));
                    excelModel.setN_license(list2.get(2));
                    excelModel.setN_emp_ft_zSum(list2.get(3));
                    excelModel.setN_emp_homeSum(list2.get(4));
                    excelModel.setN_studentSum(list2.get(5));
                    excelModel.setN_hhld_tripSum(list2.get(6));
                    excelModel.setN_emp_ptSum(list2.get(7));
                    excelModel.setGta06_hhld(list2.get(8));
                    excelModels.add(excelModel);
                }
                Sheet sheet1=new Sheet(1,0,ExcelModel.class);
                excelWriter.write(excelModels,sheet1);
            }
           /* for (String s : map.keySet()) {
                for (String s1 : map.get(s)) {
                    System.out.println(s1);
                }
            }*/
        }catch (IOException ex){
            ex.printStackTrace();
        }finally {
            excelWriter.finish();
            try {
                inputStream.close();
                outputStream.close();
            }catch (IOException ex){
                ex.printStackTrace();
            }
        }
        System.out.println("操作成功");
    }
}
           /* List<String> stringList=new LinkedList<>();
            for (String s : set) {
                int n_personSum=0;
                int n_vehicleSum=0;
                int n_license=0;
                //n_licence p	n_emp_ft z	n_emp_pt z	n_emp_home z	n_student z	n_hhld_trip p
                double n_emp_ft_zSum=0;
                double n_emp_homeSum=0;
                double n_studentSum=0;
                double n_hhld_tripSum=0;
                double n_emp_ptSum=0;
                int i=0;
                for (Object o : list) {
                    List<String> list1 = (List<String>) o;
                    if (s.equals(list1.get(8))) {
                        n_personSum += Double.parseDouble(list1.get(4));
                        n_vehicleSum += Double.parseDouble(list1.get(5));
                        n_license += Double.parseDouble(list1.get(15));
                        n_emp_ft_zSum += Double.parseDouble(list1.get(16));
                        n_emp_ptSum += Double.parseDouble(list1.get(17));
                        n_emp_homeSum += Double.parseDouble(list1.get(18));
                        n_studentSum += Double.parseDouble(list1.get(19));
                        n_hhld_tripSum += Double.parseDouble(list1.get(20));
                        i++;
                    }else {
                        continue;
                    }
                }
                stringList.add(s+"n_person平均数为"+n_personSum/i);
                stringList.add(s+"n_vehicleS平均数为"+n_vehicleSum/i);
                stringList.add(s+"n_license"+n_license/i);
                stringList.add(s+"n_emp_ft_zSum平均数为"+n_emp_ft_zSum/i);
                stringList.add(s+"n_emp_home平均数为"+n_emp_homeSum/i);
                stringList.add(s+"n_student平均数为"+n_studentSum/i);
                stringList.add(s+"n_hhld_trip平均数为"+n_hhld_tripSum/i);
                stringList.add(s+"n_emp_pt平均数为"+n_emp_ptSum/i);
               // stringList.add(s+"n_emp_ft_zSum平均数为"+n_emp_ft_zSum/i);
                map.put(s,stringList);
            }
            System.out.println(map.toString());
            //return list;
        }catch (IOException ex){
            ex.printStackTrace();
        }*/
        //return null;

